package lab03.test14;
import java.sql.*;
public class Test14 {
    public static void main(String[] args) {
// 14、首先将用户信息表中的数据在屏幕上显示出来，
        然后插入一个记录，
//     最后将更新以后数据表中的数据在屏幕上显示出来。

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        String  SQL = "create table t_userinfo(
        " +"usercode varchar(4) not null," +
        "username varchar(20)," +
                "password varchar(10)," +
                "email varchar(40)," +
                "primary key(usercode))";
        String SQL1 = "insert into
        t_userinfo (usercode,username,password,email)" +
        "select '7101','宋晓梅','123456','
        xiaomei@yahoo.com.cn' UNION " +
        "select '7102','关松杰','654321','
        songjie@263.net' UNION " +
        "select '7103','李晓梅','123654','
        xiaomei@yahoo.com.cn' UNION " +
        "select '7104','张松岩','456123','
        songyan@yahoo.com.cn' UNION " +
        "select '7105','王亚杰','321456','
        yajie@hotmail.com' UNION " +
        "select '7106','曲小声','654321','
        xiaosheng@263.net' " ;
        String SQL2 = "select * FROM t_userinfo";
        String SQL3 = "insert into t_userinfo
        values('7107','周东杰','124563','
                zhoudong@yahao.com.cn')";
        try {
            Class.forName
                    ("com.mysql.cj.jdbc.Driver");
            connection =
                    DriverManager.getConnection("jdbc:mysql:
//localhost:3306/java_2017?serverTimezone=
                            UTC&characterEncoding=utf-8", "" +
                            "root", "123456");
            statement =
                    connection.createStatement();
            statement.execute(SQL);

            statement.execute(SQL1);

            statement.execute(SQL2);
            resultSet = statement.getResultSet();

            while (resultSet.next()) {
                System.out.print(
                        resultSet.getString(1) + "\t");
                System.out.print(
                        resultSet.getString(2) + "\t");
                System.out.print(
                        resultSet.getString(3) + "\t");
                System.out.println(
                        resultSet.getString(4));
            }
            statement.execute(SQL3);

            System.out.println("------------------------
                            --------------------");

                    statement.execute(SQL2);
            resultSet = statement.getResultSet();
            while (resultSet.next()) {
                System.out.print(resultSet.getString(1) + "\t");
                System.out.print(resultSet.getString(2) + "\t");
                System.out.print(resultSet.getString(3) + "\t");
                System.out.println(resultSet.getString(4));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}
